#! C:\Perl\bin\perl.exe

# Copyright (c) 2010, College Center for Library Automation

# See the included Copyright.txt file for full license

#
# there multiple reports for a single college. script retrieves all reports for any particular college
# and accumulates the data before updating the database.
#

use strict;
use warnings;

use LWP::UserAgent;
use HTTP::Request;
use HTTP::Request::Common;
use DBI;
use DBD::ODBC;
use Date::Calc qw(Decode_Month Add_Delta_YM Add_Delta_Days Today Delta_Days);
use Text::CSV;

use avsCommon;

dbsetup('get_proquest.pl');

my ($content, $response, $request, $url);
my @periods;
my ($begperiodndx, $endperiodndx, $collegeidsave, $collegecode);

my $data;
my %stats;
my $stats;

# create user agent, set cookies
my $ua = new LWP::UserAgent(keep_alive=>1);
$ua->timeout(300);
$ua->cookie_jar( {} );

# access vendor site to get logged in
getLogin();

# capture all the college/campus id's in a hash for use during data extraction
my %college;
do {
	$college {$option1} = $collegeid;
} while $sthlogin->fetch;


# request data for year month in optional range. typically beg and end month the same.
# 12 months before requested date is returned in data
my @rundate = @enddate;

do {
	$collegeidsave = 0;

	# loop through all the colleges for each data collection. put them back in collegeid order for summation routine
	foreach my $key (sort { $college{$a} <=> $college{$b} } ( keys(%college) )) {
		$collegecode = $key;
		$collegeid = $college{$collegecode};
		$collegeidsave = $collegeid if ! $collegeidsave;

		getStats ();
	}

	while ( (my $datasource, my $value) = each %stats )
	{
		for (my $i=$begperiodndx; $i<=$endperiodndx; $i++) {
			my $session = $value->{sessions}[$i];
			my $search  = $value->{searches}[$i];
			print "$vendorid $collegeid $periods[$i] $datasource $session $search\n";
			$sthstats->execute($vendorid, $collegeid, $datasource, $periods[$i], $session, 1)
				or die '#error: sthstats execute 1 failed';
			$sthstats->execute($vendorid, $collegeid, $datasource, $periods[$i], $search, 2 )
				or die '#error: sthstats execute 2 failed';
		}
	}

	@rundate = Add_Delta_YM(@rundate, -1, 0);	# retreat a year

} until  (Delta_Days(@begdate, @rundate) < 0);

dbshutdown();

doLogout();

exit();

#
# retrieve the data for all the colleges in the login section.
#
sub getLogin {

	$url='http://lad.proquest.com/ladweb';
	
	$response = $ua->request(GET $url);
	if (!$response->is_success) {
		die "#error:$0: GET MENU $url : " . $response->status_line;
	};

	$url='http://lad.proquest.com/ladweb';

	$request = HTTP::Request->new(POST => $url);
	$request->content_type('application/x-www-form-urlencoded');
	$request->content("RQT=382&TS=1083593167&USAGE=0&USERID=$vendorusername&PASSWORD=$vendorpassword");
	$response = $ua->request($request);
	if (!$response->is_success && substr($response->status_line,0,3) ne '302') {
		die "#error:$0: POST report $url : " . $response->status_line;
	};
	
}

sub getStats {	# setup end date to pass in url. must be one month past desired data
	
	# find the begin date, either 1 year prior or the begining requested date, which ever is greater
	my @periodFrom = Add_Delta_YM(@rundate, -1, 0);	# max one years data at a time
	if (Delta_Days( @begdate, @periodFrom ) < 0) {
		@periodFrom = @begdate;
	};
	my $yyyymmTo = sprintf('%04d%02d',Add_Delta_YM(@rundate, 0, 1));
	my $runyear  = substr($yyyymmTo, 1, 4);
	my $yyyymmFr = sprintf('%04d%02d',@periodFrom );
	
	# Post the college client code
	$request = HTTP::Request->new(POST => "http://lad.proquest.com/ladweb");
	$request->content_type('application/x-www-form-urlencoded');
	$request->content("RQT=380&action=clientchange&selectedClientID=$collegecode");
	
	my $response = $ua->request($request);
	# occasionally times out here, so lets retry the request at least once on timeout
	if (substr($response->status_line,0,3) eq '500') {
		print "#info: timeout retry\n";
		sleep 5;
		$response = $ua->request($request);
	};
	if (!$response->is_success) {
		die "#error:$0: POST college client code $url : " . $response->status_line;
	};
	
	print "client selected $collegecode\n";

	#Post request the report csv
	$request = HTTP::Request->new(POST => "http://lad.proquest.com/ladweb");
	$request->content_type('application/x-www-form-urlencoded');
#	$request->content("RQT=380&ScheduleID=-1&action=create&ReportID=224%7CCOUN%7CCounter+Database+Report+1&DeliveryMethod=DLN&ShowItemsWithZeroUsage_136=NO&ShowItemsWithZeroUsage_226=NO&ShowItemsWithZeroUsage_294=NO&ShowItemsWithZeroUsage_225=NO&ShowItemsWithZeroUsage_133=NO&ShowItemsWithZeroUsage_290=NO&ShowItemsWithZeroUsage_134=NO&ShowItemsWithZeroUsage_224=NO&ShowItemsWithZeroUsage_273=NO&ShowItemsWithZeroUsage_132=NO&ShowDetailedStatistics_301=SING&ShowDetailedStatistics_161=MULT&ShowDetailedStatistics_136=SING&ShowDetailedStatistics_226=SING&ShowDetailedStatistics_294=SING&ShowDetailedStatistics_293=SING&ShowDetailedStatistics_225=SING&ShowDetailedStatistics_133=SING&ShowDetailedStatistics_290=SING&ShowDetailedStatistics_138=SING&ShowDetailedStatistics_134=SING&ShowDetailedStatistics_163=SING&ShowDetailedStatistics_224=SING&ShowDetailedStatistics_273=SING&ShowDetailedStatistics_132=SING&ShowDetailedStatistics_135=SING&ShowDetailedStatistics_302=SING&UsagePeriodFrom_301=$yyyymmFr&UsagePeriodTo_301=$yyyymmFr&UsagePeriodFrom_161=$yyyymmFr&UsagePeriodTo_161=$yyyymmFr&UsagePeriodFrom_136=$yyyymmFr&UsagePeriodTo_136=$yyyymmFr&UsagePeriodFrom_226=$yyyymmFr&UsagePeriodTo_226=$yyyymmFr&UsagePeriodFrom_294=$yyyymmFr&UsagePeriodTo_294=$yyyymmFr&UsagePeriodFrom_293=$yyyymmFr&UsagePeriodTo_293=$yyyymmFr&UsagePeriodFrom_225=$yyyymmFr&UsagePeriodTo_225=$yyyymmFr&UsagePeriodFrom_133=$yyyymmFr&UsagePeriodTo_133=$yyyymmFr&UsagePeriodFrom_290=$yyyymmFr&UsagePeriodTo_290=$yyyymmFr&UsagePeriodFrom_138=$yyyymmFr&UsagePeriodTo_138=$yyyymmFr&UsagePeriodFrom_134=12&AnnualUsagePeriodFrom=Jan+$runyear&UsagePeriodTo_134=$yyyymmFr&UsagePeriodFrom_163=$yyyymmFr" ."01&UsagePeriodTo_163=$yyyymmFr"."01&UsagePeriodFrom_224=12&UsagePeriodTo_224=$yyyymmFr&UsagePeriodFrom_273=$yyyymmFr&UsagePeriodTo_273=$yyyymmFr&UsagePeriodFrom_132=$yyyymmFr&UsagePeriodTo_132=$yyyymmFr&UsagePeriodFrom_135=$yyyymmFr&UsagePeriodTo_135=$yyyymmFr&UsagePeriodFrom_302=$yyyymmFr&UsagePeriodTo_302=$yyyymmFr&UsagePeriodTotalMonths_301=1&UsagePeriodTotalMonths_161=1&UsagePeriodTotalMonths_136=1&UsagePeriodTotalMonths_226=1&UsagePeriodTotalMonths_294=1&UsagePeriodTotalMonths_293=1&UsagePeriodTotalMonths_225=1&UsagePeriodTotalMonths_133=1&UsagePeriodTotalMonths_290=1&UsagePeriodTotalMonths_138=1&UsagePeriodTotalMonths_134=12&UsagePeriodTotalMonths_163=14&UsagePeriodTotalMonths_224=12&UsagePeriodTotalMonths_273=1&UsagePeriodTotalMonths_132=1&UsagePeriodTotalMonths_135=1&UsagePeriodTotalMonths_302=1&DeliveryFormat_301=CSV&DeliveryFormat_161=CSV&DeliveryFormat_136=CSV&DeliveryFormat_226=CSV&DeliveryFormat_294=CSV&DeliveryFormat_293=CSV&DeliveryFormat_225=CSV&DeliveryFormat_133=CSV&DeliveryFormat_290=CSV&DeliveryFormat_138=CSV&DeliveryFormat_134=HTML&DeliveryFormat_163=CSV&DeliveryFormat_224=CSV&DeliveryFormat_273=CSV&DeliveryFormat_132=CSV&DeliveryFormat_135=CSV&DeliveryFormat_302=CSV&EmailAddress=&ScheduledDeliveryOptionsDay=15&ScheduledDeliveryOptionsMonth=1&ScheduledDeliveryOptionsYear=$yyyymmFr&x=38&y=8");
	$request->content("RQT=380&action=create&ReportID=224%7CCOUN%7CCounter+Database+Report+1&DeliveryMethod=DLN&ShowItemsWithZeroUsage_312=NO&ShowItemsWithZeroUsage_136=NO&ShowItemsWithZeroUsage_226=NO&ShowItemsWithZeroUsage_331=NO&ShowItemsWithZeroUsage_294=NO&ShowItemsWithZeroUsage_311=NO&ShowItemsWithZeroUsage_225=NO&ShowItemsWithZeroUsage_133=NO&ShowItemsWithZeroUsage_290=NO&ShowItemsWithZeroUsage_134=NO&ShowItemsWithZeroUsage_224=NO&ShowItemsWithZeroUsage_273=NO&ShowItemsWithZeroUsage_132=NO&ShowItemsWithZeroUsage_313=NO&ShowDetailedStatistics_301=SING&ShowDetailedStatistics_312=MULT&ShowDetailedStatistics_161=MULT&ShowDetailedStatistics_136=SING&ShowDetailedStatistics_226=SING&ShowDetailedStatistics_331=SING&ShowDetailedStatistics_294=SING&ShowDetailedStatistics_293=SING&ShowDetailedStatistics_311=SING&ShowDetailedStatistics_225=SING&ShowDetailedStatistics_133=SING&ShowDetailedStatistics_290=SING&ShowDetailedStatistics_138=SING&ShowDetailedStatistics_134=SING&ShowDetailedStatistics_163=SING&ShowDetailedStatistics_224=SING&ShowDetailedStatistics_273=SING&ShowDetailedStatistics_132=SING&ShowDetailedStatistics_313=MULT&ShowDetailedStatistics_135=SING&ShowDetailedStatistics_302=SING&ShowDetailedStatistics_310=SING&UsagePeriodFrom_301=$yyyymmFr&UsagePeriodTo_301=$yyyymmTo&UsagePeriodFrom_312=$yyyymmFr&UsagePeriodTo_312=$yyyymmTo&UsagePeriodFrom_161=$yyyymmFr&UsagePeriodTo_161=$yyyymmTo&UsagePeriodFrom_136=$yyyymmFr&UsagePeriodTo_136=$yyyymmTo&UsagePeriodFrom_226=$yyyymmFr&UsagePeriodTo_226=$yyyymmTo&UsagePeriodFrom_331=$yyyymmFr&UsagePeriodTo_331=$yyyymmTo&UsagePeriodFrom_294=$yyyymmFr&UsagePeriodTo_294=$yyyymmTo&UsagePeriodFrom_293=$yyyymmFr&UsagePeriodTo_293=$yyyymmTo&UsagePeriodFrom_311=$yyyymmFr&UsagePeriodTo_311=$yyyymmTo&UsagePeriodFrom_225=$yyyymmFr&UsagePeriodTo_225=$yyyymmTo&UsagePeriodFrom_133=$yyyymmFr&UsagePeriodTo_133=$yyyymmTo&UsagePeriodFrom_290=$yyyymmFr&UsagePeriodTo_290=$yyyymmTo&UsagePeriodFrom_138=$yyyymmFr&UsagePeriodTo_138=$yyyymmTo&UsagePeriodFrom_134=12&AnnualUsagePeriodFrom=Jan+$runyear&UsagePeriodTo_134=$yyyymmTo&UsagePeriodFrom_163=$yyyymmFr"."01&UsagePeriodTo_163=$yyyymmTo"."01&UsagePeriodFrom_224=$yyyymmFr&UsagePeriodTo_224=$yyyymmTo&UsagePeriodFrom_273=$yyyymmFr&UsagePeriodTo_273=$yyyymmTo&UsagePeriodFrom_132=$yyyymmFr&UsagePeriodTo_132=$yyyymmTo&UsagePeriodFrom_313=$yyyymmFr&UsagePeriodTo_313=$yyyymmTo&UsagePeriodFrom_135=$yyyymmFr&UsagePeriodTo_135=$yyyymmTo&UsagePeriodFrom_302=$yyyymmFr&UsagePeriodTo_302=$yyyymmTo&UsagePeriodFrom_310=$yyyymmFr&UsagePeriodTo_310=$yyyymmTo&UsagePeriodTotalMonths_301=1&UsagePeriodTotalMonths_312=1&UsagePeriodTotalMonths_161=1&UsagePeriodTotalMonths_136=1&UsagePeriodTotalMonths_226=1&UsagePeriodTotalMonths_331=1&UsagePeriodTotalMonths_294=1&UsagePeriodTotalMonths_293=1&UsagePeriodTotalMonths_311=1&UsagePeriodTotalMonths_225=1&UsagePeriodTotalMonths_133=1&UsagePeriodTotalMonths_290=1&UsagePeriodTotalMonths_138=1&UsagePeriodTotalMonths_134=12&UsagePeriodTotalMonths_163=14&UsagePeriodTotalMonths_224=1&UsagePeriodTotalMonths_273=1&UsagePeriodTotalMonths_132=1&UsagePeriodTotalMonths_313=1&UsagePeriodTotalMonths_135=1&UsagePeriodTotalMonths_302=1&UsagePeriodTotalMonths_310=1&DeliveryFormat_312=XML&DeliveryFormat_161=HTML&DeliveryFormat_136=HTML&DeliveryFormat_226=HTML&DeliveryFormat_331=HTML&DeliveryFormat_294=HTML&DeliveryFormat_293=HTML&DeliveryFormat_311=HTML&DeliveryFormat_225=HTML&DeliveryFormat_133=HTML&DeliveryFormat_290=HTML&DeliveryFormat_138=HTML&DeliveryFormat_134=HTML&DeliveryFormat_163=HTML&DeliveryFormat_224=CSV&DeliveryFormat_273=HTML&DeliveryFormat_132=HTML&DeliveryFormat_313=XML&DeliveryFormat_135=HTML&DeliveryFormat_310=HTML&EmailAddress=closel%40ccla%2Elib%2Efl%2Eus&ScheduledDeliveryOptionsDay=5&ScheduledDeliveryOptionsMonth=1&ScheduledDeliveryOptionsYear=$yyyymmFr&x=47&y=5&");

	$response = $ua->request($request);
	# occasionally times out here, so lets retry the request at least once on timeout
	if (substr($response->status_line,0,3) eq '500') {
		print "#info: timeout retry\n";
		sleep 5;
		$response = $ua->request($request);
	};
	if (!$response->is_success) {
		die "#error:$0: POST csv $url : " . $response->status_line;
	};
	
	$content = $response->decoded_content;

	# save contents for possible debugging
	savecontent('Proquest', "$runyear-$collegecode", $content);

	# split the data into individual lines
	my @contents = split ("\n", $content);
	
	if ('No Usage/Data' eq substr ($contents[5], 0, 13) ) {
		return 'no data';
	}
	
	#grab the months line from content
		my $dates = $contents[4];

	# grab the dates off of the header lines, first four columns are not dates
	@periods = ('','','','');	
	my $begperiod = myperiod($begyear, $begmonth);
	my $endperiod = myperiod($endyear, $endmonth);
	$begperiodndx = 4;
	$endperiodndx = 4;

	while ($dates =~ /(?<month>\w+)-(?<year>\d{4})/g) {
		my $period = myperiod($+{year},$+{month});
		$begperiodndx++ if ($period lt $begperiod);
		$endperiodndx++ if ($period lt $endperiod);
		push @periods, $period;
	}
	$begperiodndx-- if $begperiodndx == 16;	# prevent multiyear problem
	$endperiodndx-- if $endperiodndx == 16;	# prevent multiyear problem

#
# some broken down by campus. getstats finds data for each
# college or campus and if needed accumulates the values up to the college level
#

	my @zfill=(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
	
	if ($collegeidsave != $collegeid) {
		while ( (my $datasource, my $value) = each %stats )
		{
			foreach my $i($begperiodndx..$endperiodndx) {
				my $session = $value->{sessions}[$i];
				my $search  = $value->{searches}[$i];
				print "$vendorid $collegeidsave $periods[$i] $datasource $session $search\n";
				$sthstats->execute($vendorid, $collegeidsave, $datasource, $periods[$i], $session, 1)
					or die '#error: sthstats execute 1.2 failed';
				$sthstats->execute($vendorid, $collegeidsave, $datasource, $periods[$i], $search, 2 )
					or die '#error: sthstats execute 2.2 failed';
			}
		}
		%stats = ();
		$stats = ();
		$collegeidsave = $collegeid;
	};

	my $csv = Text::CSV->new({binary => 1});
	my $datasource='';
	my @columns = ();
	my $parseErrors = 0;

	# parse each csv line
	foreach my $contents (@contents[6..$#contents]) {

		if ($csv->parse($contents)) {
			@columns = $csv->fields();

			# fix bad csv, occasionally datasource has comma in name. this appends first column onto second column 
			# and deletes first column
			#	proquest fixed csv creator		$columns[0] = shift(@columns).','.$columns[0] if $#columns > 16;

			$datasource = $columns[0] if ($columns[0] ne $datasource);

			if (! exists $stats{$datasource} ) {
				@{ $stats{$datasource}{sessions} } = @zfill;
				@{ $stats{$datasource}{searches} } = @zfill;
			}

			foreach my $i($begperiodndx..$endperiodndx) {
				if ($columns[3] =~ /Total Sessions/i) {
					$stats{$datasource}{sessions}[$i] += $columns[$i];
				}
				elsif ($columns[3] =~ /Total Searches/i) {
					$stats{$datasource}{searches}[$i] += $columns[$i];
				}
			}
		}

		else {
			print "#warning:$0: csv parse error : " , $csv->error_input, "\n";
			# occasionally sends back HTML rather than CSV if there is a server error.
			# no real way to know, there for we punt if there are more than 3 parse errors
			# for one report
			if ($parseErrors++ > 3) {
				warn "#error:$0: to many parse errors for $collegeid, trying next report\n";
	;			last;
			}
		}

	}
}


sub doLogout {
	$url = "http://lad.proquest.com/ladweb?RQT=381";
	$response = $ua->request(GET $url);
}	
